package com.rybbaby.tss.front.tssSemester.dao.imp;

import com.rybbaby.tss.core.utils.StringUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssSemester.dao.TssSemesterDao;
import com.rybbaby.tss.front.tssSemester.entity.TssSemester;
import org.apache.poi.util.StringUtil;
import org.springframework.stereotype.Repository;

import java.util.Map;

/**
 * @author 雷春琼
 * @description 学期表数据库操作实现
 * @createDate 2017-08-09 下午11:50:14
 */
@Repository
public class TssSemesterDaoImp extends GenericDao<TssSemester> implements TssSemesterDao {

    @Override
    public Map<String, Object> queryByDateAndYear(String startDate, String endDate, String particularYear,String id,String schoolId,String type) {
        StringBuffer buf=new StringBuffer();
        StringBuffer sql=new StringBuffer();

        //判断学期类型是否存在
        buf.append("SELECT\n" +
                "\tcount(1) count \n" +
                "\tFROM\n" +
                "\ttss_semester ts\n" +
                "\tLEFT JOIN sys_admin_user sau ON sau.id = ts.adminUserId\n" +
                "       where \n" +
                "\t ts.particularYear = ?\n" +
                "\tAND sau.schoolId = ? and type=? ");
        if (StringUtils.isNotBlank(id)){
            buf.append(" AND ts.id != '" + id + "'");
        }
       Map<String,Object> semesterMap= this.findOneForJdbc(buf.toString(),new Object[]{particularYear,schoolId,type});
       if(Integer.parseInt(semesterMap.get("count").toString())!=0){
           return semesterMap;
       }else{
           String startDateSql="SELECT\n" +
                   "   min(ts.startDate) startDate\n" +
                   "   FROM\n" +
                   "   tss_semester ts\n" +
                   "   LEFT JOIN sys_admin_user sau ON sau.id = ts.adminUserId\n" +
                   "   WHERE\n" +
                   "    ts.particularYear = ? \n" +
                   "   AND sau.schoolId = ? ";
           Map<String,Object> minStartDateMap=this.findOneForJdbc(startDateSql,new Object[]{particularYear,schoolId});

           String endDateSql="SELECT\n" +
                   "   max(ts.endDate) endDate\n" +
                   "   FROM\n" +
                   "   tss_semester ts\n" +
                   "   LEFT JOIN sys_admin_user sau ON sau.id = ts.adminUserId\n" +
                   "   WHERE\n" +
                   "    ts.particularYear = ?\n" +
                   "   AND sau.schoolId = ?";
           Map<String,Object> maxEndDateMap=this.findOneForJdbc(endDateSql,new Object[]{particularYear,schoolId});
           String minStartDate=minStartDateMap.get("startDate")+"";
           String maxEndDate=maxEndDateMap.get("endDate")+"";

               sql.append("SELECT\n" +
                       "   count(1) count \n" +
                       "   FROM\n" +
                       "   tss_semester ts\n" +
                       "   LEFT JOIN sys_admin_user sau ON sau.id = ts.adminUserId\n" +
                       "   WHERE\n" +
                       "    (( ? <= ? \n" +
                       "      and ? >= ? )\n" +
                       "    or (? >= ? \n" +
                       "       and  ? <= ?) \n" +
                       "    or (? >= ? \n" +
                       "      and  ? <= ? )) \n" +
                       "   AND ts.particularYear = ? \n" +
                       "   AND sau.schoolId = ? ");
               if (StringUtils.isNotBlank(id)) {
                   sql.append(" AND ts.id != '" + id + "'");
               }
               return this.findOneForJdbc(sql.toString(), new Object[]{startDate,minStartDate,endDate,minStartDate, endDate, maxEndDate,startDate,maxEndDate,startDate,minStartDate,endDate,minStartDate,particularYear,schoolId});
           }
    }
}
